﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace ZG_member
{
    public partial class StatisticsBlock : Form
    {
        public StatisticsBlock()
        {
            InitializeComponent();
        }

        private void StatisticsBlock_Load(object sender, EventArgs e)
        {
            ZG_member.BLL.consumeSolution consume = new ZG_member.BLL.consumeSolution();
            ZG_member.BLL.goods good = new ZG_member.BLL.goods();

            this.cbxUnit.Text = "年";

            DataTable dtCardType = consume.GetList("").Tables[0];
            DataRow row2;
            row2 = dtCardType.NewRow();
            row2["solutionName"] = "全部";
            row2["id"] = "0";
            dtCardType.Rows.Add(row2);
            this.cbxCardType.DataSource = dtCardType;
            cbxCardType.ValueMember = "id";
            cbxCardType.DisplayMember = "solutionName";
            cbxCardType.Text = "全部";


            DataTable dt = good.GetList("").Tables[0];
            DataRow row;
            row=dt.NewRow();
            row["goodsName"]="全部";
            row["id"]="0";
            dt.Rows.Add(row);
            this.cbxGoods.DataSource = dt;
            this.cbxGoods.ValueMember = "id";
            this.cbxGoods.DisplayMember = "goodsName";
            cbxGoods.Text = "全部";
            

        }

        private void button1_Click(object sender, EventArgs e)
        {
            string sql = "";
            ZG_member.BLL.memberInfo member = new ZG_member.BLL.memberInfo();
            #region 全系统-财务收入-年/月/日
            if (tabControl1.SelectedTab.Text == "全系统")
            {
                #region 年
                if (this.cbxUnit.Text == "年")
                {
                    sql = "select convert(varchar,DATEPART (YYYY , selltime ))+ '年' as '时间', SUM(goodscount*goodsprice) as '收入金额' from";
                    sql += " (select * from goodsinfo where";
                    sql += " gid in";
                    sql += " (";
                    if (this.cbxGoods.Text != "全部")
                    {
                        sql += "  select goodsId from goods where  id=" + this.cbxGoods.SelectedValue.ToString() + "";
                    }
                    else
                    {
                        sql += "  select goodsId from goods ";
                    }
                    sql += " ) and selltime>='" + this.dateTimePicker1.Text + "' and selltime<='" + this.dateTimePicker2.Text + "'";
                    sql += " ) as tb";
                    sql += "  where ";
                    sql += "wasteBookId in ";
                    sql += "  (";
                    sql += "    select Id from wasteBook";
                    sql += " where memberCardId";
                    sql += " in ";
                    sql += " (";
                    if (this.cbxCardType.Text != "全部")
                    {
                        sql += " select memberId from memberInfo where cardType=" + this.cbxCardType.SelectedValue.ToString() + " ";
                    }
                    else
                    {
                        sql += " select memberId from memberInfo ";
                    }
                    sql += " )";
                    sql += " )";
                    sql += "  group by   DATEPART (YYYY , selltime )";
             
                }
                #endregion
                #region 月
                if (this.cbxUnit.Text == "月")
                {
                    sql = "select convert(varchar,DATEPART (YYYY , tb.selltime ))+ '年'+convert(varchar,DATEPART (MM , tb.selltime ))+ '月' as '时间', SUM(goodscount*goodsprice) as '收入金额' from";
                    sql += " (select * from goodsinfo where";
                    sql += " gid in";
                    sql += " (";
                    if (this.cbxGoods.Text != "全部")
                    {
                        sql += "  select goodsId from goods where  id=" + this.cbxGoods.SelectedValue.ToString() + "";
                    }
                    else
                    {
                        sql += "  select goodsId from goods ";
                    }
                    sql += " ) and selltime>='" + this.dateTimePicker1.Text + "' and selltime<='" + this.dateTimePicker2.Text + "'";
                    sql += " ) as tb";
                    sql += "  where ";
                    sql += "wasteBookId in ";
                    sql += "  (";
                    sql += "    select Id from wasteBook";
                    sql += " where memberCardId";
                    sql += " in ";
                    sql += " (";
                    if (this.cbxCardType.Text != "全部")
                    {
                        sql += " select memberId from memberInfo where cardType=" + this.cbxCardType.SelectedValue.ToString() + " ";
                    }
                    else
                    {
                        sql += " select memberId from memberInfo ";
                    }
                    sql += " )";
                    sql += " )";
                    sql += "  group by   DATEPART (YYYY , selltime ),DATEPART (MM , tb.selltime )";
                }
                #endregion
                #region 日
                

                if (this.cbxUnit.Text == "日")
                {
                    sql = "select convert(varchar,DATEPART (YYYY , tb.selltime ))+ '年'+convert(varchar,DATEPART (MM , tb.selltime ))+ '月'+convert(varchar,DATEPART (DD , tb.selltime ))+ '日' as '时间', SUM(goodscount*goodsprice) as '收入金额' from";
                    sql += " (select * from goodsinfo where";
                    sql += " gid in";
                    sql += " (";
                    if (this.cbxGoods.Text != "全部")
                    {
                        sql += "  select goodsId from goods where  id=" + this.cbxGoods.SelectedValue.ToString() + "";
                    }
                    else
                    {
                        sql += "  select goodsId from goods ";
                    }
                    sql += " ) and selltime>='" + this.dateTimePicker1.Text + "' and selltime<='" + this.dateTimePicker2.Text + "'";
                    sql += " ) as tb";
                    sql += "  where ";
                    sql += "wasteBookId in ";
                    sql += "  (";
                    sql += "    select Id from wasteBook";
                    sql += " where memberCardId";
                    sql += " in ";
                    sql += " (";
                    if (this.cbxCardType.Text != "全部")
                    {
                        sql += " select memberId from memberInfo where cardType=" + this.cbxCardType.SelectedValue.ToString() + " ";
                    }
                    else
                    {
                        sql += " select memberId from memberInfo ";
                    }
                    sql += " )";
                    sql += " )";
                    sql += "  group by   DATEPART (YYYY , selltime ),DATEPART (MM , tb.selltime ),DATEPART (DD , tb.selltime )";
                }
                #endregion
                dataGridView1.DataSource = member.GetListSql(sql).Tables[0];
            }
            #endregion
            #region 每个销售-财务收入-年/月/日
            else
            {
                #region 年
                if (this.cbxUnit.Text == "年")
                {
                    sql =  " select CONVERT(varchar,DATEPART(YYYY,goodsInfo.selltime))+'年' as '时间', SalesStaffList.SalesPersonId as '销售编号',COUNT(memberInfo.memberId) as '人数' ,SUM(goodsInfo.goodscount*goodsInfo.goodsprice) as '售价' from memberInfo,goodsInfo,SalesStaffList,wasteBook,goods ";
                    sql += " where ";
                    if (this.cbxGoods.Text != "全部")
                    {
                        sql += " goods.id=" + this.cbxGoods.SelectedValue.ToString() + " ";
                        sql += " and  ";
                    }
                    sql += " goods.goodsId=goodsInfo.gid ";
                    sql += " and ";
                    sql += " goodsInfo.selltime>='" + this.dateTimePicker1.Text + "' and goodsInfo.selltime<='" + this.dateTimePicker2.Text + "' ";
                    sql += " and ";
                    sql += " goodsInfo.wasteBookId=wasteBook.id ";
                    sql += " and ";
                    if (this.cbxCardType.Text != "全部")
                    {
                        sql += " memberInfo.CardType=" + this.cbxCardType.SelectedValue.ToString() + " ";
                        sql += " and ";
                    }
                    sql += " wasteBook.memberCardId=memberInfo.memberId ";
                    sql += " group by  DATEPART(YYYY,goodsInfo.selltime),SalesStaffList.SalesPersonId ";
                }
                #endregion
                #region 月
                if (this.cbxUnit.Text == "月")
                {
                    sql = " select CONVERT(varchar,DATEPART(YYYY,goodsInfo.selltime))+'年'+CONVERT(varchar,DATEPART(MM,goodsInfo.selltime))+'月' as '时间', SalesStaffList.SalesPersonId as '销售编号',COUNT(memberInfo.memberId) as '人数' ,SUM(goodsInfo.goodscount*goodsInfo.goodsprice) as '售价' from memberInfo,goodsInfo,SalesStaffList,wasteBook,goods ";
                    sql += " where ";
                    if (this.cbxGoods.Text != "全部")
                    {
                        sql += " goods.id=" + this.cbxGoods.SelectedValue.ToString() + " ";
                        sql += " and  ";
                    }
                    sql += " goods.goodsId=goodsInfo.gid ";
                    sql += " and ";
                    sql += " goodsInfo.selltime>='" + this.dateTimePicker1.Text + "' and goodsInfo.selltime<='" + this.dateTimePicker2.Text + "' ";
                    sql += " and ";
                    sql += " goodsInfo.wasteBookId=wasteBook.id ";
                    sql += " and ";
                    if (this.cbxCardType.Text != "全部")
                    {
                        sql += " memberInfo.CardType=" + this.cbxCardType.SelectedValue.ToString() + " ";
                        sql += " and ";
                    }
                    sql += " wasteBook.memberCardId=memberInfo.memberId ";
                    sql += " group by  DATEPART(YYYY,goodsInfo.selltime),DATEPART(MM,goodsInfo.selltime),SalesStaffList.SalesPersonId ";
                }
                #endregion
                #region 日
                if (this.cbxUnit.Text == "日")
                {
                    sql = " select CONVERT(varchar,DATEPART(YYYY,goodsInfo.selltime))+'年'+CONVERT(varchar,DATEPART(MM,goodsInfo.selltime))+'月'+CONVERT(varchar,DATEPART(DD,goodsInfo.selltime))+'日' as '时间', SalesStaffList.SalesPersonId as '销售编号',COUNT(memberInfo.memberId) as '人数' ,SUM(goodsInfo.goodscount*goodsInfo.goodsprice) as '售价' from memberInfo,goodsInfo,SalesStaffList,wasteBook,goods ";
                    sql += " where ";
                    if (this.cbxGoods.Text != "全部")
                    {
                        sql += " goods.id=" + this.cbxGoods.SelectedValue.ToString() + " ";
                        sql += " and  ";
                    }
                    sql += " goods.goodsId=goodsInfo.gid ";
                    sql += " and ";
                    sql += " goodsInfo.selltime>='" + this.dateTimePicker1.Text + "' and goodsInfo.selltime<='" + this.dateTimePicker2.Text + "' ";
                    sql += " and ";
                    sql += " goodsInfo.wasteBookId=wasteBook.id ";
                    sql += " and ";
                    if (this.cbxCardType.Text != "全部")
                    {
                        sql += " memberInfo.CardType=" + this.cbxCardType.SelectedValue.ToString() + " ";
                        sql += " and ";
                    }
                    sql += " wasteBook.memberCardId=memberInfo.memberId ";
                    sql += " group by  DATEPART(YYYY,goodsInfo.selltime),DATEPART(MM,goodsInfo.selltime),DATEPART(DD,goodsInfo.selltime),SalesStaffList.SalesPersonId ";
                }
                #endregion
                dataGridView2.DataSource = member.GetListSql(sql).Tables[0];


            }
            #endregion
        }

        private void button2_Click(object sender, EventArgs e)
        {
            string sql = "";
            ZG_member.BLL.memberInfo member = new ZG_member.BLL.memberInfo();
            #region 全系统-财务支出-年/月/日
            if (this.tabControl1.SelectedTab.Text == "全系统")
            {
                #region 年
                if (this.cbxUnit.Text == "年")
                {
                    sql = "select convert(varchar,DATEPART (YYYY , tb.selltime ))+ '年' as '时间', SUM(tb.goodscount*goods.finalCost) as '支出金额' from";
                    sql += " (select * from goodsinfo where";
                    sql += " gid in";
                    sql += " (";
                    if (this.cbxGoods.Text != "全部")
                    {
                        sql += "  select goodsId from goods where  id=" + this.cbxGoods.SelectedValue.ToString() + "";
                    }
                    else
                    {
                        sql += "  select goodsId from goods ";
                    }
                    sql += " ) and selltime>='" + this.dateTimePicker1.Text + "' and selltime<='" + this.dateTimePicker2.Text + "'";
                    sql += " ) as tb,goods";
                    sql += "  where ";
                    sql += "wasteBookId in ";
                    sql += "  (";
                    sql += "    select Id from wasteBook";
                    sql += " where memberCardId";
                    sql += " in ";
                    sql += " (";
                    if (this.cbxCardType.Text != "全部")
                    {
                        sql += " select memberId from memberInfo where cardType=" + this.cbxCardType.SelectedValue.ToString() + " ";
                    }
                    else
                    {
                        sql += " select memberId from memberInfo ";
                    }
                    sql += " )";
                    sql += " ) and tb.gid=goods.goodsId ";
                    sql += "  group by   DATEPART (YYYY , tb.selltime )";
                    
                }
                #endregion
                #region 月
                if (this.cbxUnit.Text == "月")
                {
                    sql = "select convert(varchar,DATEPART (YYYY , tb.selltime ))+ '年'+convert(varchar,DATEPART (MM , tb.selltime ))+ '月' as '时间', SUM(tb.goodscount*goods.finalCost) as '支出金额' from";
                    sql += " (select * from goodsinfo where";
                    sql += " gid in";
                    sql += " (";
                    if (this.cbxGoods.Text != "全部")
                    {
                        sql += "  select goodsId from goods where  id=" + this.cbxGoods.SelectedValue.ToString() + "";
                    }
                    else
                    {
                        sql += "  select goodsId from goods ";
                    }
                    sql += " ) and selltime>='" + this.dateTimePicker1.Text + "' and selltime<='" + this.dateTimePicker2.Text + "'";
                    sql += " ) as tb,goods";
                    sql += "  where ";
                    sql += "wasteBookId in ";
                    sql += "  (";
                    sql += "    select Id from wasteBook";
                    sql += " where memberCardId";
                    sql += " in ";
                    sql += " (";
                    if (this.cbxCardType.Text != "全部")
                    {
                        sql += " select memberId from memberInfo where cardType=" + this.cbxCardType.SelectedValue.ToString() + " ";
                    }
                    else
                    {
                        sql += " select memberId from memberInfo ";
                    }
                    sql += " )";
                    sql += " ) and tb.gid=goods.goodsId ";
                    sql += "  group by   DATEPART (YYYY , tb.selltime ),DATEPART (MM , tb.selltime )";
                }
                #endregion
                #region 日
                if (this.cbxUnit.Text == "日")
                {
                    sql = "select convert(varchar,DATEPART (YYYY , tb.selltime ))+ '年'+convert(varchar,DATEPART (MM , tb.selltime ))+ '月'+convert(varchar,DATEPART (DD , tb.selltime ))+ '日' as '时间', SUM(tb.goodscount*goods.finalCost) as '支出金额' from";
                    sql += " (select * from goodsinfo where";
                    sql += " gid in";
                    sql += " (";
                    if (this.cbxGoods.Text != "全部")
                    {
                        sql += "  select goodsId from goods where  id=" + this.cbxGoods.SelectedValue.ToString() + "";
                    }
                    else
                    {
                        sql += "  select goodsId from goods ";
                    }
                    sql += " ) and selltime>='" + this.dateTimePicker1.Text + "' and selltime<='" + this.dateTimePicker2.Text + "'";
                    sql += " ) as tb,goods";
                    sql += "  where ";
                    sql += "wasteBookId in ";
                    sql += "  (";
                    sql += "    select Id from wasteBook";
                    sql += " where memberCardId";
                    sql += " in ";
                    sql += " (";
                    if (this.cbxCardType.Text != "全部")
                    {
                        sql += " select memberId from memberInfo where cardType=" + this.cbxCardType.SelectedValue.ToString() + " ";
                    }
                    else
                    {
                        sql += " select memberId from memberInfo ";
                    }
                    sql += " )";
                    sql += " ) and tb.gid=goods.goodsId ";
                    sql += "  group by   DATEPART (YYYY , tb.selltime ),DATEPART (MM , tb.selltime ),DATEPART (DD , tb.selltime )";
                }
                #endregion
                dataGridView1.DataSource = member.GetListSql(sql).Tables[0];
            }
            #endregion
            #region 每个销售-财务支出-年/月/日
            else
            {
                #region 年
                if (this.cbxUnit.Text == "年")
                {
                    sql = " select CONVERT(varchar,DATEPART(YYYY,goodsInfo.selltime))+'年' as '时间', SalesStaffList.SalesPersonId as '销售编号',COUNT(memberInfo.memberId) as '人数' ,SUM(goodsInfo.goodscount*goods.finalCost)  as '支出价格' from memberInfo,goodsInfo,SalesStaffList,wasteBook,goods ";
                    sql += " where ";
                    if (this.cbxGoods.Text != "全部")
                    {
                        sql += " goods.id=" + this.cbxGoods.SelectedValue.ToString() + " ";
                        sql += " and  ";
                    }
                    sql += " goods.goodsId=goodsInfo.gid ";
                    sql += " and ";
                    sql += " goodsInfo.selltime>='" + this.dateTimePicker1.Text + "' and goodsInfo.selltime<='" + this.dateTimePicker2.Text + "' ";
                    sql += " and ";
                    sql += " goodsInfo.wasteBookId=wasteBook.id ";
                    sql += " and ";
                    if (this.cbxCardType.Text != "全部")
                    {
                        sql += " memberInfo.CardType=" + this.cbxCardType.SelectedValue.ToString() + " ";
                        sql += " and ";
                    }
                    sql += " wasteBook.memberCardId=memberInfo.memberId ";
                    sql += " group by  DATEPART(YYYY,goodsInfo.selltime),SalesStaffList.SalesPersonId ";
                }
                #endregion
                #region 月
                if (this.cbxUnit.Text == "月")
                {
                    sql = " select CONVERT(varchar,DATEPART(YYYY,goodsInfo.selltime))+'年'+CONVERT(varchar,DATEPART(MM,goodsInfo.selltime))+'月' as '时间', SalesStaffList.SalesPersonId as '销售编号',COUNT(memberInfo.memberId) as '人数' ,SUM(goodsInfo.goodscount*goods.finalCost)  as '支出价格' from memberInfo,goodsInfo,SalesStaffList,wasteBook,goods ";
                    sql += " where ";
                    if (this.cbxGoods.Text != "全部")
                    {
                        sql += " goods.id=" + this.cbxGoods.SelectedValue.ToString() + " ";
                        sql += " and  ";
                    }
                    sql += " goods.goodsId=goodsInfo.gid ";
                    sql += " and ";
                    sql += " goodsInfo.selltime>='" + this.dateTimePicker1.Text + "' and goodsInfo.selltime<='" + this.dateTimePicker2.Text + "' ";
                    sql += " and ";
                    sql += " goodsInfo.wasteBookId=wasteBook.id ";
                    sql += " and ";
                    if (this.cbxCardType.Text != "全部")
                    {
                        sql += " memberInfo.CardType=" + this.cbxCardType.SelectedValue.ToString() + " ";
                        sql += " and ";
                    }
                    sql += " wasteBook.memberCardId=memberInfo.memberId ";
                    sql += " group by  DATEPART(YYYY,goodsInfo.selltime),DATEPART(MM,goodsInfo.selltime),SalesStaffList.SalesPersonId ";
                }
    
                #endregion
                #region 日
                if (this.cbxUnit.Text == "日")
                {
                    sql = " select CONVERT(varchar,DATEPART(YYYY,goodsInfo.selltime))+'年'+CONVERT(varchar,DATEPART(MM,goodsInfo.selltime))+'月'+CONVERT(varchar,DATEPART(DD,goodsInfo.selltime))+'日' as '时间', SalesStaffList.SalesPersonId as '销售编号',COUNT(memberInfo.memberId) as '人数' ,SUM(goodsInfo.goodscount*goods.finalCost)  as '支出价格' from memberInfo,goodsInfo,SalesStaffList,wasteBook,goods ";
                    sql += " where ";
                    if (this.cbxGoods.Text != "全部")
                    {
                        sql += " goods.id=" + this.cbxGoods.SelectedValue.ToString() + " ";
                        sql += " and  ";
                    }
                    sql += " goods.goodsId=goodsInfo.gid ";
                    sql += " and ";
                    sql += " goodsInfo.selltime>='" + this.dateTimePicker1.Text + "' and goodsInfo.selltime<='" + this.dateTimePicker2.Text + "' ";
                    sql += " and ";
                    sql += " goodsInfo.wasteBookId=wasteBook.id ";
                    sql += " and ";
                    if (this.cbxCardType.Text != "全部")
                    {
                        sql += " memberInfo.CardType=" + this.cbxCardType.SelectedValue.ToString() + " ";
                        sql += " and ";
                    }
                    sql += " wasteBook.memberCardId=memberInfo.memberId ";
                    sql += " group by  DATEPART(YYYY,goodsInfo.selltime),DATEPART(MM,goodsInfo.selltime),DATEPART(DD,goodsInfo.selltime),SalesStaffList.SalesPersonId ";
                }
            
                #endregion
                dataGridView2.DataSource = member.GetListSql(sql).Tables[0];
            }
            #endregion
       
        }

        private void button3_Click(object sender, EventArgs e)
        {
            string sql = "";
            ZG_member.BLL.memberInfo member = new ZG_member.BLL.memberInfo();
            #region 全系统-财务利润-年/月/日
            if (this.tabControl1.SelectedTab.Text == "全系统")
            {
                #region 年
                if (this.cbxUnit.Text == "年")
                {
                    sql = "select convert(varchar,DATEPART (YYYY , tb.selltime ))+ '年' as '时间', SUM(tb.goodscount*tb.goodsprice-tb.goodscount*goods.finalCost) as '利润'  from";
                    sql += " (select * from goodsinfo where";
                    sql += " gid in";
                    sql += " (";
                    if (this.cbxGoods.Text != "全部")
                    {
                        sql += "  select goodsId from goods where  id=" + this.cbxGoods.SelectedValue.ToString() + "";
                    }
                    else
                    {
                        sql += "  select goodsId from goods ";
                    }
                    sql += " ) and selltime>='" + this.dateTimePicker1.Text + "' and selltime<='" + this.dateTimePicker2.Text + "'";
                    sql += " ) as tb,goods";
                    sql += "  where ";
                    sql += "wasteBookId in ";
                    sql += "  (";
                    sql += "    select Id from wasteBook";
                    sql += " where memberCardId";
                    sql += " in ";
                    sql += " (";
                    if (this.cbxCardType.Text != "全部")
                    {
                        sql += " select memberId from memberInfo where cardType=" + this.cbxCardType.SelectedValue.ToString() + " ";
                    }
                    else
                    {
                        sql += " select memberId from memberInfo ";
                    }
                    sql += " )";
                    sql += " ) and tb.gid=goods.goodsId ";
                    sql += "  group by   DATEPART (YYYY , tb.selltime )";

                }
                #endregion
                #region 月
                if (this.cbxUnit.Text == "月")
                {
                    sql = "select convert(varchar,DATEPART (YYYY , tb.selltime ))+ '年'+convert(varchar,DATEPART (MM , tb.selltime ))+ '月' as '时间', SUM(tb.goodscount*tb.goodsprice-tb.goodscount*goods.finalCost) as '利润'  from";
                    sql += " (select * from goodsinfo where";
                    sql += " gid in";
                    sql += " (";
                    if (this.cbxGoods.Text != "全部")
                    {
                        sql += "  select goodsId from goods where  id=" + this.cbxGoods.SelectedValue.ToString() + "";
                    }
                    else
                    {
                        sql += "  select goodsId from goods ";
                    }
                    sql += " ) and selltime>='" + this.dateTimePicker1.Text + "' and selltime<='" + this.dateTimePicker2.Text + "'";
                    sql += " ) as tb,goods";
                    sql += "  where ";
                    sql += "wasteBookId in ";
                    sql += "  (";
                    sql += "    select Id from wasteBook";
                    sql += " where memberCardId";
                    sql += " in ";
                    sql += " (";
                    if (this.cbxCardType.Text != "全部")
                    {
                        sql += " select memberId from memberInfo where cardType=" + this.cbxCardType.SelectedValue.ToString() + " ";
                    }
                    else
                    {
                        sql += " select memberId from memberInfo ";
                    }
                    sql += " )";
                    sql += " ) and tb.gid=goods.goodsId ";
                    sql += "  group by   DATEPART (YYYY , tb.selltime ),DATEPART (MM , tb.selltime )";

                }
                #endregion
                #region 日
                if (this.cbxUnit.Text == "日")
                {
                    sql = "select convert(varchar,DATEPART (YYYY , tb.selltime ))+ '年'+convert(varchar,DATEPART (MM , tb.selltime ))+ '月'+convert(varchar,DATEPART (DD , tb.selltime ))+ '日' as '时间', SUM(tb.goodscount*tb.goodsprice-tb.goodscount*goods.finalCost) as '利润'  from";
                    sql += " (select * from goodsinfo where";
                    sql += " gid in";
                    sql += " (";
                    if (this.cbxGoods.Text != "全部")
                    {
                        sql += "  select goodsId from goods where  id=" + this.cbxGoods.SelectedValue.ToString() + "";
                    }
                    else
                    {
                        sql += "  select goodsId from goods ";
                    }
                    sql += " ) and selltime>='" + this.dateTimePicker1.Text + "' and selltime<='" + this.dateTimePicker2.Text + "'";
                    sql += " ) as tb,goods";
                    sql += "  where ";
                    sql += "wasteBookId in ";
                    sql += "  (";
                    sql += "    select Id from wasteBook";
                    sql += " where memberCardId";
                    sql += " in ";
                    sql += " (";
                    if (this.cbxCardType.Text != "全部")
                    {
                        sql += " select memberId from memberInfo where cardType=" + this.cbxCardType.SelectedValue.ToString() + " ";
                    }
                    else
                    {
                        sql += " select memberId from memberInfo ";
                    }
                    sql += " )";
                    sql += " ) and tb.gid=goods.goodsId ";
                    sql += "  group by   DATEPART (YYYY , tb.selltime ),DATEPART (MM , tb.selltime ),DATEPART (DD , tb.selltime )";

                }
                #endregion
                dataGridView1.DataSource = member.GetListSql(sql).Tables[0];
            }
             #endregion
            #region 每个销售-财务收入-年/月/日
            else
            {
                #region 年
                if (this.cbxUnit.Text == "年")
                {
                    sql =  " select CONVERT(varchar,DATEPART(YYYY,goodsInfo.selltime))+'年' as '时间', SalesStaffList.SalesPersonId as '销售编号',COUNT(memberInfo.memberId) as '人数' ,SUM(goodsInfo.goodscount*goodsInfo.goodsprice-goodsInfo.goodscount*goods.finalCost) as '利润' from memberInfo,goodsInfo,SalesStaffList,wasteBook,goods ";
                    sql += " where ";
                    if (this.cbxGoods.Text != "全部")
                    {
                        sql += " goods.id=" + this.cbxGoods.SelectedValue.ToString() + " ";
                        sql += " and  ";
                    }
                    sql += " goods.goodsId=goodsInfo.gid ";
                    sql += " and ";
                    sql += " goodsInfo.selltime>='" + this.dateTimePicker1.Text + "' and goodsInfo.selltime<='" + this.dateTimePicker2.Text + "' ";
                    sql += " and ";
                    sql += " goodsInfo.wasteBookId=wasteBook.id ";
                    sql += " and ";
                    if (this.cbxCardType.Text != "全部")
                    {
                        sql += " memberInfo.CardType=" + this.cbxCardType.SelectedValue.ToString() + " ";
                        sql += " and ";
                    }
                    sql += " wasteBook.memberCardId=memberInfo.memberId ";
                    sql += " group by  DATEPART(YYYY,goodsInfo.selltime),SalesStaffList.SalesPersonId ";
                }
                #endregion
                #region 月
                if (this.cbxUnit.Text == "月")
                {
                    sql = " select CONVERT(varchar,DATEPART(YYYY,goodsInfo.selltime))+'年'+CONVERT(varchar,DATEPART(MM,goodsInfo.selltime))+'月' as '时间', SalesStaffList.SalesPersonId as '销售编号',COUNT(memberInfo.memberId) as '人数' ,SUM(goodsInfo.goodscount*goodsInfo.goodsprice-goodsInfo.goodscount*goods.finalCost) as '利润' from memberInfo,goodsInfo,SalesStaffList,wasteBook,goods ";
                    sql += " where ";
                    if (this.cbxGoods.Text != "全部")
                    {
                        sql += " goods.id=" + this.cbxGoods.SelectedValue.ToString() + " ";
                        sql += " and  ";
                    }
                    sql += " goods.goodsId=goodsInfo.gid ";
                    sql += " and ";
                    sql += " goodsInfo.selltime>='" + this.dateTimePicker1.Text + "' and goodsInfo.selltime<='" + this.dateTimePicker2.Text + "' ";
                    sql += " and ";
                    sql += " goodsInfo.wasteBookId=wasteBook.id ";
                    sql += " and ";
                    if (this.cbxCardType.Text != "全部")
                    {
                        sql += " memberInfo.CardType=" + this.cbxCardType.SelectedValue.ToString() + " ";
                        sql += " and ";
                    }
                    sql += " wasteBook.memberCardId=memberInfo.memberId ";
                    sql += " group by  DATEPART(YYYY,goodsInfo.selltime),DATEPART(MM,goodsInfo.selltime),SalesStaffList.SalesPersonId ";
                }

                #endregion
                #region 日
                if (this.cbxUnit.Text == "日")
                {
                    sql = " select CONVERT(varchar,DATEPART(YYYY,goodsInfo.selltime))+'年'+CONVERT(varchar,DATEPART(MM,goodsInfo.selltime))+'月'+CONVERT(varchar,DATEPART(DD,goodsInfo.selltime))+'日' as '时间', SalesStaffList.SalesPersonId as '销售编号',COUNT(memberInfo.memberId) as '人数' ,SUM(goodsInfo.goodscount*goodsInfo.goodsprice-goodsInfo.goodscount*goods.finalCost) as '利润' from memberInfo,goodsInfo,SalesStaffList,wasteBook,goods ";
                    sql += " where ";
                    if (this.cbxGoods.Text != "全部")
                    {
                        sql += " goods.id=" + this.cbxGoods.SelectedValue.ToString() + " ";
                        sql += " and  ";
                    }
                    sql += " goods.goodsId=goodsInfo.gid ";
                    sql += " and ";
                    sql += " goodsInfo.selltime>='" + this.dateTimePicker1.Text + "' and goodsInfo.selltime<='" + this.dateTimePicker2.Text + "' ";
                    sql += " and ";
                    sql += " goodsInfo.wasteBookId=wasteBook.id ";
                    sql += " and ";
                    if (this.cbxCardType.Text != "全部")
                    {
                        sql += " memberInfo.CardType=" + this.cbxCardType.SelectedValue.ToString() + " ";
                        sql += " and ";
                    }
                    sql += " wasteBook.memberCardId=memberInfo.memberId ";
                    sql += " group by  DATEPART(YYYY,goodsInfo.selltime),DATEPART(MM,goodsInfo.selltime),DATEPART(DD,goodsInfo.selltime),SalesStaffList.SalesPersonId ";
                }

                #endregion
                dataGridView2.DataSource = member.GetListSql(sql).Tables[0];
            }
            #endregion
      
        }

        private void button4_Click(object sender, EventArgs e)
        {

        }

        private void button7_Click(object sender, EventArgs e)
        {
            string sql = "";
            ZG_member.BLL.memberInfo member = new ZG_member.BLL.memberInfo();
            #region 全系统-商品数量及类别-年/月/日
            if (this.tabControl1.SelectedTab.Text == "全系统")
            {

                    sql = "select typeName as '类别',goodsName as '商品名称',amount as '商品数量' from goods,goodsType";
                    sql += " where goodsTypeId=goodsType.id ";

                
                
                dataGridView1.DataSource = member.GetListSql(sql).Tables[0];
            }
            #endregion
            
        }

        private void button6_Click(object sender, EventArgs e)
        {
            string sql = "";
            ZG_member.BLL.memberInfo member = new ZG_member.BLL.memberInfo();
            #region 全系统-会员数量及类别-年/月/日
            if (tabControl1.SelectedTab.Text == "全系统")
            {
                #region 年
                if (this.cbxUnit.Text == "年")
                {
                    sql  = " select convert(varchar,DATEPART(YYYY,wasteBook.date))+'年' as '时间', consumeSolution.solutionName as '会员等级',count(wasteBook.memberCardId) as '数量' from wasteBook,memberInfo,consumeSolution ";
                    sql += " where wasteBook.details='发卡' ";
                    sql += " and ";
                    sql += " wasteBook.date>='2011-01-01' ";
                    sql += " and ";
                    sql += " wasteBook.date<='2014-12-10' ";
                    sql += " and ";
                    sql += " memberInfo.memberId=wasteBook.memberCardId ";
                    sql += " and ";
                    sql += " memberInfo.cardType=consumeSolution.id ";
                    sql += " group by DATEPART(YYYY,wasteBook.date), consumeSolution.solutionName ";
                }
                #endregion
                #region 月
                if (this.cbxUnit.Text == "月")
                {
                    sql = " select convert(varchar,DATEPART(YYYY,wasteBook.date))+'年'+convert(varchar,DATEPART(MM,wasteBook.date))+'月' as '时间', consumeSolution.solutionName as '会员等级',count(wasteBook.memberCardId) as '数量' from wasteBook,memberInfo,consumeSolution ";
                    sql += " where wasteBook.details='发卡' ";
                    sql += " and ";
                    sql += " wasteBook.date>='2011-01-01' ";
                    sql += " and ";
                    sql += " wasteBook.date<='2014-12-10' ";
                    sql += " and ";
                    sql += " memberInfo.memberId=wasteBook.memberCardId ";
                    sql += " and ";
                    sql += " memberInfo.cardType=consumeSolution.id ";
                    sql += " group by DATEPART(YYYY,wasteBook.date),DATEPART(MM,wasteBook.date), consumeSolution.solutionName ";
                }
                #endregion
                #region 日


                if (this.cbxUnit.Text == "日")
                {
                    sql = " select convert(varchar,DATEPART(YYYY,wasteBook.date))+'年'+convert(varchar,DATEPART(MM,wasteBook.date))+'月'+convert(varchar,DATEPART(DD,wasteBook.date))+'日' as '时间', consumeSolution.solutionName as '会员等级',count(wasteBook.memberCardId) as '数量' from wasteBook,memberInfo,consumeSolution ";
                    sql += " where wasteBook.details='发卡' ";
                    sql += " and ";
                    sql += " wasteBook.date>='2011-01-01' ";
                    sql += " and ";
                    sql += " wasteBook.date<='2014-12-10' ";
                    sql += " and ";
                    sql += " memberInfo.memberId=wasteBook.memberCardId ";
                    sql += " and ";
                    sql += " memberInfo.cardType=consumeSolution.id ";
                    sql += " group by DATEPART(YYYY,wasteBook.date),DATEPART(MM,wasteBook.date),DATEPART(DD,wasteBook.date), consumeSolution.solutionName ";
                }
                #endregion
                dataGridView1.DataSource = member.GetListSql(sql).Tables[0];
            }
            #endregion
            #region 每个销售-会员数量及类别-年/月/日
            else
            {
                #region 年
                if (this.cbxUnit.Text == "年")
                {
                    sql = " select convert(varchar,DATEPART(YYYY,wasteBook.date))+'年' as '时间',SalesStaffList.SalesPersonId as '销售编号', consumeSolution.solutionName as '会员等级',count(wasteBook.memberCardId) as '数量' from wasteBook,memberInfo,consumeSolution,dbo.SalesStaffList ";
                    sql += " where wasteBook.details='发卡' ";
                    sql += " and ";
                    sql += " wasteBook.date>='2011-01-01' ";
                    sql += " and ";
                    sql += " wasteBook.date<='2014-12-10' ";
                    sql += " and ";
                    sql += " memberInfo.memberId=wasteBook.memberCardId ";
                    sql += " and ";
                    sql += " memberInfo.cardType=consumeSolution.id ";
                    sql += " group by DATEPART(YYYY,wasteBook.date),SalesStaffList.SalesPersonId, consumeSolution.solutionName ";
                }
                #endregion
                #region 月
                if (this.cbxUnit.Text == "月")
                {
                    sql = " select convert(varchar,DATEPART(YYYY,wasteBook.date))+'年'+convert(varchar,DATEPART(MM,wasteBook.date))+'月' as '时间',SalesStaffList.SalesPersonId as '销售编号', consumeSolution.solutionName as '会员等级',count(wasteBook.memberCardId) as '数量' from wasteBook,memberInfo,consumeSolution,SalesStaffList ";
                    sql += " where wasteBook.details='发卡' ";
                    sql += " and ";
                    sql += " wasteBook.date>='2011-01-01' ";
                    sql += " and ";
                    sql += " wasteBook.date<='2014-12-10' ";
                    sql += " and ";
                    sql += " memberInfo.memberId=wasteBook.memberCardId ";
                    sql += " and ";
                    sql += " memberInfo.cardType=consumeSolution.id ";
                    sql += " group by DATEPART(YYYY,wasteBook.date),DATEPART(MM,wasteBook.date),SalesStaffList.SalesPersonId, consumeSolution.solutionName ";
                }
                #endregion
                #region 日


                if (this.cbxUnit.Text == "日")
                {
                    sql = " select convert(varchar,DATEPART(YYYY,wasteBook.date))+'年'+convert(varchar,DATEPART(MM,wasteBook.date))+'月'+convert(varchar,DATEPART(DD,wasteBook.date))+'日' as '时间',SalesStaffList.SalesPersonId as '销售编号', consumeSolution.solutionName as '会员等级',count(wasteBook.memberCardId) as '数量' from wasteBook,memberInfo,consumeSolution,SalesStaffList ";
                    sql += " where wasteBook.details='发卡' ";
                    sql += " and ";
                    sql += " wasteBook.date>='2011-01-01' ";
                    sql += " and ";
                    sql += " wasteBook.date<='2014-12-10' ";
                    sql += " and ";
                    sql += " memberInfo.memberId=wasteBook.memberCardId ";
                    sql += " and ";
                    sql += " memberInfo.cardType=consumeSolution.id ";
                    sql += " group by DATEPART(YYYY,wasteBook.date),DATEPART(MM,wasteBook.date),DATEPART(DD,wasteBook.date),SalesStaffList.SalesPersonId, consumeSolution.solutionName ";
                }
                #endregion
                dataGridView2.DataSource = member.GetListSql(sql).Tables[0];


            }
            #endregion
        }
    }
}
